contents

CONNECT BY는 SQL에서 특히 계층형 데이터(즉, 부모-자식 또는 트리 구조를 가진 데이터)를 쿼리하기 위해 사용되는 절입니다. 이는 Oracle Database에서 가장 유명하게 구현된 비표준 SQL 확장 기능입니다.

이 절을 사용하면 특정 시작점에서부터 부모-자식 또는 자식-부모 방향으로 트리 구조(예: 직원-관리자 조직도, 제품 카테고리 계층)를 탐색할 수 있습니다.

핵심 문법

CONNECT BY 절은 SELECT 문의 일부이며 항상 두 가지 핵심 연산자를 포함합니다.

1. START WITH: 계층 구조의 루트(root) 로 사용할 행(들)을 지정합니다. 이곳이 탐색의 시작점입니다.
2. CONNECT BY: 부모 행과 자식 행 간의 관계를 지정합니다.

SELECT ...
FROM table_name
WHERE ...
START WITH start_condition  -- 예: manager_id IS NULL
CONNECT BY PRIOR child_column = parent_column; -- 또는 PRIOR parent_column = child_column

PRIOR 연산자

PRIOR 연산자는 계층 탐색의 핵심입니다. 어떤 열이 부모 행에 속하는지를 쿼리에 알려줍니다.

• PRIOR child_column = parent_column: 하향식(top-down) 탐색을 지정합니다. " START WITH 절의 행(들)에서 시작하여, parent_column이 이전 행의 child_column과 같은 모든 행을 찾아라"라는 의미입니다.
• PRIOR parent_column = child_column: 상향식(bottom-up) 탐색을 지정합니다. "child_column이 이전 행의 parent_column과 같은 모든 행을 찾아라"라는 의미입니다.

상세 예제: 직원 계층 구조

각 직원이 관리자에게 보고하는 employees 테이블이라는 고전적인 예제를 사용해 보겠습니다.

employees 테이블:

예제 1: 하향식 탐색 (모든 부하 직원 찾기)

CEO인 Alice에서 시작하는 전체 조직도를 보고 싶습니다.

SELECT 
    emp_id, 
    name, 
    manager_id
FROM 
    employees
START WITH 
    manager_id IS NULL  -- CEO인 Alice (manager_id가 NULL)에서 시작
CONNECT BY 
    PRIOR emp_id = manager_id; -- manager_id가 PRIOR 행의 emp_id와 일치하는 행을 찾음

작동 방식:

1. START WITH: manager_id IS NULL인 행을 선택합니다. Alice (emp_id=1)입니다.
2. 1 레벨 (PRIOR emp_id = 1): manager_id = 1인 모든 행을 찾습니다. Bob (emp_id=2)과 Carol (emp_id=3)을 찾습니다.
3. 2 레벨 (PRIOR emp_id = 2 또는 3):
   • manager_id = 2인 모든 행을 찾습니다. David (emp_id=4)를 찾습니다.
   • manager_id = 3인 모든 행을 찾습니다. Emily (emp_id=5)를 찾습니다.
4. 3 레벨: 쿼리가 manager_id = 4 또는 5를 찾지만 일치하는 것이 없으므로 탐색이 중지됩니다.

결과:

(참고: Bob과 Carol 같은 형제(sibling) 간의 순서는 ORDER SIBLINGS BY 절 없이는 보장되지 않습니다.)

예제 2: 상향식 탐색 (모든 관리자 찾기)

직원 David에서 시작하는 지휘 계통을 보고 싶습니다.

SELECT 
    emp_id, 
    name, 
    manager_id
FROM 
    employees
START WITH 
    emp_id = 4  -- David에서 시작
CONNECT BY 
    PRIOR manager_id = emp_id; -- emp_id가 PRIOR 행의 manager_id와 일치하는 행을 찾음

결과:

유용한 계층형 함수 및 의사 열

CONNECT BY는 계층 구조 쿼리를 더 쉽게 만들기 위한 특수 함수들도 제공합니다.

LEVEL

계층 구조에서 해당 행의 레벨(깊이)을 반환하는 의사 열(pseudocolumn)입니다. 루트 행(START WITH)은 레벨 1입니다.

SELECT 
    emp_id, 
    LPAD(' ', (LEVEL - 1) * 2) || name AS indented_name, -- 이름을 들여쓰기
    LEVEL
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR emp_id = manager_id;

결과:

CONNECT_BY_ISLEAF

행이 리프 노드(계층 구조에서 자식이 없음)이면 1을, 그렇지 않으면 0을 반환하는 의사 열입니다.

CONNECT_BY_ROOT

계층 구조의 루트 노드의 열 값을 반환하는 연산자입니다.

-- 모든 직원에 대해 CEO(루트) 찾기
SELECT 
    name,
    CONNECT_BY_ROOT name AS ceo
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR emp_id = manager_id;

SYS_CONNECT_BY_PATH

루트에서 현재 행까지의 전체 경로를 반환하는 함수입니다.

SELECT 
    name,
    SYS_CONNECT_BY_PATH(name, '/') AS path
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR emp_id = manager_id;

David의 결과:

루프 처리: NOCYCLE

만약 데이터에 무한 루프가 있다면(예: Alice가 Bob에게 보고하고, Bob이 다시 Alice에게 보고하는 경우), 일반적인 CONNECT BY 쿼리는 오류를 내며 실패합니다. NOCYCLE 매개변수를 사용하면 루프를 감지했을 때 쿼리가 해당 경로의 탐색을 중지하도록 할 수 있습니다.

CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

현대 SQL의 대안: 재귀 CTE

CONNECT BY 구문은 Oracle 고유의 확장 기능입니다. 계층형 데이터를 쿼리하는 표준적이고 교차 데이터베이스 방식은 재귀 CTE(Common Table Expressions) 를 사용하는 것입니다.

다음은 예제 1의 하향식 탐색을 재귀 CTE로 동일하게 구현한 것입니다 (PostgreSQL, SQL Server, MySQL 등에서 작동).

WITH RECURSIVE OrgChart (emp_id, name, manager_id) AS (
    -- 앵커 멤버 (루트)
    SELECT 
        emp_id, 
        name, 
        manager_id
    FROM 
        employees
    WHERE 
        manager_id IS NULL

    UNION ALL

    -- 재귀 멤버 (자식)
    SELECT 
        e.emp_id, 
        e.name, 
        e.manager_id
    FROM 
        employees e
    JOIN 
        OrgChart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM OrgChart;

references